using System;
using System.Data;
using System.Data.SqlClient;

using SLIITPro.Core;
using SLIITPro.Data.Entity;

namespace SLIITPro.Data.Extension
{
    public class UserExtension
    {
        private Datamanager _datamanager;
        public UserExtension()
        {
            _datamanager = new Datamanager();
        }
        public void CreateUser(Users users)
        {
            var query = string.Format(Properties.Resources.queryCreateUser, users.Password);
            var parameters = new SqlParameter[15];
            parameters[0] = new SqlParameter("@1", SqlDbType.NVarChar) { Value = users.FirstName };
            parameters[1] = new SqlParameter("@2", SqlDbType.NVarChar) { Value = users.LastName };
            parameters[2] = new SqlParameter("@3", SqlDbType.NVarChar) { Value = users.Street };
            parameters[3] = new SqlParameter("@4", SqlDbType.NVarChar) { Value = users.City };
            parameters[4] = new SqlParameter("@5", SqlDbType.NVarChar) { Value = users.Province };
            parameters[5] = new SqlParameter("@6", SqlDbType.NVarChar) { Value = users.Country };
            parameters[6] = new SqlParameter("@7", SqlDbType.NVarChar) { Value = users.Email };
            //parameters[7] = new SqlParameter("@8", SqlDbType.NVarChar) { Value = users.Password };
            parameters[7] = new SqlParameter("@9", SqlDbType.NVarChar) { Value = users.UserKey };
            parameters[8] = new SqlParameter("@10", SqlDbType.NVarChar) { Value = users.ActivationCode };
            parameters[9] = new SqlParameter("@11", SqlDbType.TinyInt) { Value = users.IsActivated };
            parameters[10] = new SqlParameter("@12", SqlDbType.DateTime) { Value = users.CreatedAt };
            parameters[11] = new SqlParameter("@13", SqlDbType.DateTime) { Value = users.UpdatedAt };
            parameters[12] = new SqlParameter("@14", SqlDbType.NVarChar) { Value = users.UserType };
            parameters[13] = new SqlParameter("@15", SqlDbType.NVarChar) { Value = users.Phone };
            parameters[14] = new SqlParameter("@16", SqlDbType.NVarChar) { Value = users.Fax };
            _datamanager.ExecuteNonQuery(query, parameters);
        }

        public void UpdateUser(Users users)
        {
            var query = string.Format(Properties.Resources.queryUpdateUser, users.Password);
            var parameters = new SqlParameter[16];
            parameters[0] = new SqlParameter("@1", SqlDbType.NVarChar) { Value = users.FirstName };
            parameters[1] = new SqlParameter("@2", SqlDbType.NVarChar) { Value = users.LastName };
            parameters[2] = new SqlParameter("@3", SqlDbType.NVarChar) { Value = users.Street };
            parameters[3] = new SqlParameter("@4", SqlDbType.NVarChar) { Value = users.City };
            parameters[4] = new SqlParameter("@5", SqlDbType.NVarChar) { Value = users.Province };
            parameters[5] = new SqlParameter("@6", SqlDbType.NVarChar) { Value = users.Country };
            parameters[6] = new SqlParameter("@7", SqlDbType.NVarChar) { Value = users.Email };
            //parameters[7] = new SqlParameter("@8", SqlDbType.NVarChar) { Value = users.Password };
            parameters[7] = new SqlParameter("@9", SqlDbType.NVarChar) { Value = users.UserKey };
            parameters[8] = new SqlParameter("@10", SqlDbType.NVarChar) { Value = users.ActivationCode };
            parameters[9] = new SqlParameter("@11", SqlDbType.TinyInt) { Value = users.IsActivated };
            parameters[10] = new SqlParameter("@12", SqlDbType.DateTime) { Value = users.CreatedAt };
            parameters[11] = new SqlParameter("@13", SqlDbType.DateTime) { Value = users.UpdatedAt };
            parameters[12] = new SqlParameter("@14", SqlDbType.NVarChar) { Value = users.UserType };
            parameters[13] = new SqlParameter("@15", SqlDbType.NVarChar) { Value = users.Phone };
            parameters[14] = new SqlParameter("@16", SqlDbType.NVarChar) { Value = users.Fax };
            parameters[15] = new SqlParameter("@17", SqlDbType.Int) { Value = users.UserId };
            _datamanager.ExecuteNonQuery(query, parameters);
        }

        public void DeleteUser(int id)
        {
            var query = Properties.Resources.queryDeleteUser;
            var parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@1", SqlDbType.Int) { Value = id };
            _datamanager.ExecuteNonQuery(query, parameter);
        }

        public DataTable SelectUser(string context, string value)
        {
            string query;
            if (string.IsNullOrEmpty(context) && string.IsNullOrEmpty(value))
                query = Properties.Resources.querySelectAllUsers;
            else
                query = string.Format(Properties.Resources.querySelectAllUsers + " WHERE {0}='{1}'", context, value);
            return _datamanager.ExecuteQuery(query, "Users", null);
        }

        public void ChangeActivationMode(bool activate, int userId)
        {
            var sqlquery =
                string.Format(Properties.Resources.queryChangeActivationMode, Convert.ToInt16(activate), DateTime.Now, userId);
            _datamanager.ExecuteNonQuery(sqlquery, null);
        }

        public bool AuthenticateUser(string username, string password, out DataRow row)
        {
            var query = string.Format(Properties.Resources.queryAuthenticateUser, username, password);
            var table = _datamanager.ExecuteQuery(query, "Users", null);
            if (table.Rows.Count == 0)
            {
                row = null;
                return false;
            }
            row = table.Rows[0];
            return row != null;
        }


        public DataRow AuthenticateUser(string username, string password)
        {
            var query = string.Format(Properties.Resources.queryAuthenticateUser, username, password);
            var table = _datamanager.ExecuteQuery(query, "Users", null);
            DataRow row = null;                       

            if (table.Rows.Count == 0)
            {
                row = null;                
            }
            row = table.Rows[0];
            return row;
        }

        private SqlConnections _conn;
        public bool IsEmailExist(string email)
        {
            _conn = new SqlConnections();
            var query = Properties.Resources.queryIsEmailExist;
            SqlCommand com = new SqlCommand(query, _conn.CreateConn());
            com.Parameters.AddWithValue("@1", email);
            int count = int.Parse(com.ExecuteScalar().ToString());
            _conn.CloseConn();
            return (count >= 1) ? true : false;
        }


        public void SetUserPasswordRequest(int id, string key, DateTime date)
        {
            var query = Properties.Resources.queryUserPasswordRequest;
            var parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@1", SqlDbType.NVarChar) { Value = key };
            parameters[1] = new SqlParameter("@2", SqlDbType.NVarChar) { Value = id };
            parameters[2] = new SqlParameter("@3", SqlDbType.NVarChar) { Value = date };
            _datamanager.ExecuteNonQuery(query, parameters);
        }

        public bool IsValideRequest(string reqId, out DataRow row)
        {
            const string query = "SELECT *FROM [PwRequest] WHERE [RequestId]=@1";
            var parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@1", SqlDbType.NVarChar) { Value = reqId };
            var table = _datamanager.ExecuteQuery(query, "PwRequest", parameters);
            if (table.Rows.Count == 0)
            {
                row = null;
                return false;
            }
            row = table.Rows[0];
            return row != null;
        }
    }
}